package com.gemcoder.system.service;

import static com.gemcoder.system.datasource.JdbcTemplateUtil.executeSQL;

import java.time.Duration;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import java.util.Map;

import cn.hutool.core.date.DateUnit;
import org.springframework.stereotype.Service;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserName;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserId;
import com.gemcoder.system.bo.Result;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllPermissions;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllMenus;
import java.sql.SQLException;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.db.Db;
import java.io.File;
import java.time.LocalDateTime;
import com.alibaba.fastjson.JSONArray;

@Service
public class FaultlistService {

    public Result createFaultReport(Map<String, Object> paramMap) {
        // 获取当前登录用户信息
        String createUsername = sdkOfGetCurrentUserName();
        Integer createUserId = sdkOfGetCurrentUserId();
        // 从paramMap中获取表单数据
        Integer projectId = paramMap.get("ProjectName1value1") != null ? Integer.valueOf(String.valueOf(paramMap.get("ProjectName1value1"))) : null;
        String deviceName = paramMap.get("EquipmentNamevalue1") != null ? String.valueOf(paramMap.get("EquipmentNamevalue1")) : null;
        String faultCategory = paramMap.get("FaultCategory1value1") != null ? String.valueOf(paramMap.get("FaultCategory1value1")) : null;
        Integer quantity = paramMap.get("Quantityvalue1") != null ? Integer.valueOf(String.valueOf(paramMap.get("Quantityvalue1"))) : null;
        String affectStartup = paramMap.get("Whetherthestartupisaffectedvalue1") != null ? String.valueOf(paramMap.get("Whetherthestartupisaffectedvalue1")) : null;
        String reportTime = paramMap.get("Reportingtimevalue1") != null ? String.valueOf(paramMap.get("Reportingtimevalue1")) : null;
        String systemDeviceName = paramMap.get("SystemDeviceNamevalue1") != null ? String.valueOf(paramMap.get("SystemDeviceNamevalue1")) : null;
        String deviceSpec = paramMap.get("EquipmentSpecificationsvalue1") != null ? String.valueOf(paramMap.get("EquipmentSpecificationsvalue1")) : null;
        String unit = paramMap.get("Unitvalue1") != null ? String.valueOf(paramMap.get("Unitvalue1")) : null;
        String faultDescription = paramMap.get("FaultDescriptionvalue1") != null ? String.valueOf(paramMap.get("FaultDescriptionvalue1")) : null;
        String reporter = paramMap.get("Reportingpersonnelvalue1") != null ? String.valueOf(paramMap.get("Reportingpersonnelvalue1")) : null;
        String faultPhotos = paramMap.get("PhotooffaultfileList1") != null ? String.valueOf(paramMap.get("PhotooffaultfileList1")) : null;
        String currentStatus = paramMap.get("CurrentStatusvalue1") != null ? String.valueOf(paramMap.get("CurrentStatusvalue1")) : null;
        String requiredPartsName = paramMap.get("Nameofrequiredaccessoriesvalue1") != null ? String.valueOf(paramMap.get("Nameofrequiredaccessoriesvalue1")) : null;
        String handlingProcess = paramMap.get("Processvalue1") != null ? String.valueOf(paramMap.get("Processvalue1")) : null;
        String handlingProcessPhotos = paramMap.get("ProcessingprocessphotosfileList1") != null ? String.valueOf(paramMap.get("ProcessingprocessphotosfileList1")) : null;
        String requiredPartsSpec = paramMap.get("Specificationsandmodelsofrequiredaccessoriesvalue1") != null ? String.valueOf(paramMap.get("Specificationsandmodelsofrequiredaccessoriesvalue1")) : null;
        String handlingResult = paramMap.get("Processingresultsvalue1") != null ? String.valueOf(paramMap.get("Processingresultsvalue1")) : null;
        String handlingResultPhotos = paramMap.get("ProcessingresultphotosfileList1") != null ? String.valueOf(paramMap.get("ProcessingresultphotosfileList1")) : null;
        String costDetails = paramMap.get("ExpenseDetailsvalue1") != null ? String.valueOf(paramMap.get("ExpenseDetailsvalue1")) : null;
        String closeTime = paramMap.get("Closingtimevalue1") != null ? String.valueOf(paramMap.get("Closingtimevalue1")) : null;
        String maintenancePersonnel = paramMap.get("Servicemaintenancepersonnelvalue2") != null ? String.valueOf(paramMap.get("Servicemaintenancepersonnelvalue2")) : null;
        // 构建插入SQL
        StringBuilder insertSql = new StringBuilder("INSERT INTO fault_report (project_id, device_name, fault_category, quantity, affect_startup, report_time, system_device_name, device_spec, unit, fault_description, reporter, fault_photos, current_status, required_parts_name, handling_process, handling_process_photos, required_parts_spec, handling_result, handling_result_photos, cost_details, close_time, maintenance_personnel, create_username, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())");
        List<Object> params = new ArrayList<>();
        params.add(projectId);
        params.add(deviceName);
        params.add(faultCategory);
        params.add(quantity);
        params.add(affectStartup);
        params.add(reportTime);
        params.add(systemDeviceName);
        params.add(deviceSpec);
        params.add(unit);
        params.add(faultDescription);
        params.add(reporter);
        params.add(faultPhotos);
        params.add(currentStatus);
        params.add(requiredPartsName);
        params.add(handlingProcess);
        params.add(handlingProcessPhotos);
        params.add(requiredPartsSpec);
        params.add(handlingResult);
        params.add(handlingResultPhotos);
        params.add(costDetails);
        params.add(closeTime);
        params.add(maintenancePersonnel);
        params.add(createUsername);
        // 执行插入SQL
        int insertRows = executeSQL(insertSql.toString(), params.toArray());
        // 返回结果
        Result result = new Result();
        if (insertRows > 0) {
            result.setFlag(true);
            result.setMessage("故障上报成功");
        } else {
            result.setFlag(false);
            result.setMessage("故障上报失败");
        }
        return result;
    }

    public Result searchFaultReports(Map<String, Object> paramMap) {
        // 获取参数
        String projectName = paramMap.get("projectName") != null ? String.valueOf(paramMap.get("projectName")) : null;
        String faultCategory = paramMap.get("faultCategory") != null ? String.valueOf(paramMap.get("faultCategory")) : null;
        String dateRange = paramMap.get("dateRange") != null ? String.valueOf(paramMap.get("dateRange")) : null;
        Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
        Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;
        // 构建查询SQL
        StringBuilder sql = new StringBuilder("SELECT \n" +
                "    fr.id, \n" +
                "    DATE_FORMAT(fr.close_time, '%Y-%m-%d %H:%i:%s') AS close_time, \n" +
                "    p.project_name, \n" +
                "    fr.device_name, \n" +
                "    fr.fault_category, \n" +
                "    fr.quantity, \n" +
                "    fr.affect_startup, \n" +
                "    DATE_FORMAT(fr.report_time, '%Y-%m-%d %H:%i:%s') AS report_time, \n" +
                "    fr.system_device_name, \n" +
                "    fr.device_spec, \n" +
                "    fr.unit, \n" +
                "    fr.fault_description, \n" +
                "    fr.reporter, \n" +
                "    fr.fault_photos, \n" +
                "    fr.current_status, \n" +
                "    fr.required_parts_name, \n" +
                "    fr.handling_process, \n" +
                "    fr.handling_process_photos, \n" +
                "    fr.required_parts_spec, \n" +
                "    fr.handling_result, \n" +
                "    fr.handling_result_photos, \n" +
                "    fr.cost_details, \n" +
                "    fr.maintenance_personnel, \n" +
                "    fr.alarm_type, \n" +
                "    fr.alarm_content, \n" +
                "    DATE_FORMAT(fr.report_date, '%Y-%m-%d %H:%i:%s') AS report_date, \n" +
                "    fr.create_username, \n" +
                "    DATE_FORMAT(fr.create_time, '%Y-%m-%d %H:%i:%s') AS create_time, \n" +
                "    fr.update_username, \n" +
                "    DATE_FORMAT(fr.update_time, '%Y-%m-%d %H:%i:%s') AS update_time\n" +
                "FROM fault_report fr \n" +
                "LEFT JOIN project p \n" +
                "ON fr.project_id = p.id \n" +
                "WHERE 1=1");
        List<Object> params = new ArrayList<>();
        if (projectName != null && !projectName.isEmpty()) {
            sql.append(" AND p.project_name = ?");
            params.add(projectName);
        }
        if (faultCategory != null && !faultCategory.isEmpty()) {
            sql.append(" AND fr.fault_category = ?");
            params.add(faultCategory);
        }
        if (dateRange != null && !dateRange.isEmpty()) {
            String[] dates = dateRange.split("~");
            if (dates.length == 2) {
                sql.append(" AND fr.report_time BETWEEN ? AND ?");
                params.add(dates[0]);
                params.add(dates[1]);
            }
        }
        // 分页查询
        int offset = (current - 1) * pageSize;
        sql.append(" LIMIT ?, ?");
        params.add(offset);
        params.add(pageSize);
        // 执行查询SQL
        List<Map<String, Object>> data = executeSQL(sql.toString(), params.toArray());
        data.forEach(stringObjectMap -> {
            // 处理日期格式
            Object reportTime = stringObjectMap.get("report_time");
            Object closeTime = stringObjectMap.get("close_time");
            if (reportTime != null && closeTime != null) {
                LocalDateTime startTime = convertStringToLocalDateTime(reportTime.toString());
                LocalDateTime endTime = convertStringToLocalDateTime(closeTime.toString());
                Duration duration = Duration.between(startTime, endTime);
                // 计算故障处理时长
                stringObjectMap.put("fault_handling_duration", duration.toHours());
            }
        });
        // 查询总条数
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) AS total FROM fault_report fr LEFT JOIN project p ON fr.project_id = p.id WHERE 1=1");
        List<Object> countParams = new ArrayList<>();
        if (projectName != null && !projectName.isEmpty()) {
            countSql.append(" AND p.project_name = ?");
            countParams.add(projectName);
        }
        if (faultCategory != null && !faultCategory.isEmpty()) {
            countSql.append(" AND fr.fault_category = ?");
            countParams.add(faultCategory);
        }
        if (dateRange != null && !dateRange.isEmpty()) {
            String[] dates = dateRange.split("~");
            if (dates.length == 2) {
                countSql.append(" AND fr.report_time BETWEEN ? AND ?");
                countParams.add(dates[0]);
                countParams.add(dates[1]);
            }
        }
        List<Map<String, Object>> countResult = executeSQL(countSql.toString(), countParams.toArray());
        int total = countResult.isEmpty() ? 0 : Integer.parseInt(String.valueOf(countResult.get(0).get("total")));
        // 返回结果
        Result result = new Result();
        result.setFlag(true);
        result.setMessage("查询成功");
        Map<String, Object> resultData = new HashMap<>();
        resultData.put("total", total);
        resultData.put("data", data);
        result.setData(resultData);
        return result;
    }

    public static LocalDateTime convertStringToLocalDateTime(String dateTimeStr) {
        // 定义日期时间格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        // 解析字符串为 LocalDateTime
        return LocalDateTime.parse(dateTimeStr, formatter);
    }

    // }
    public Result exportFaultReports(Map<String, Object> paramMap) {
        // 获取参数
        String projectName = paramMap.get("projectName") != null ? String.valueOf(paramMap.get("projectName")) : null;
        String faultCategory = paramMap.get("faultCategory") != null ? String.valueOf(paramMap.get("faultCategory")) : null;
        String dateRange = paramMap.get("dateRange") != null ? String.valueOf(paramMap.get("dateRange")) : null;
        Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
        Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;
        // 构建查询SQL
        StringBuilder sql = new StringBuilder("SELECT fr.id, fr.device_name, fr.fault_category, fr.quantity, fr.affect_startup, fr.report_time, fr.system_device_name, fr.device_spec, fr.unit, fr.fault_description, fr.reporter, fr.fault_photos, fr.current_status, fr.required_parts_name, fr.handling_process, fr.handling_process_photos, fr.required_parts_spec, fr.handling_result, fr.handling_result_photos, fr.cost_details, fr.close_time, fr.maintenance_personnel, fr.alarm_type, fr.alarm_content, fr.report_date, fr.create_username, fr.create_time, fr.update_username, fr.update_time FROM fault_report fr LEFT JOIN project p ON fr.project_id = p.id WHERE 1=1");
        List<Object> params = new ArrayList<>();
        if (projectName != null && !projectName.isEmpty()) {
            sql.append(" AND p.project_name = ?");
            params.add(projectName);
        }
        if (faultCategory != null && !faultCategory.isEmpty()) {
            sql.append(" AND fr.fault_category = ?");
            params.add(faultCategory);
        }
        if (dateRange != null && !dateRange.isEmpty()) {
            String[] dates = dateRange.split(" - ");
            if (dates.length == 2) {
                sql.append(" AND fr.report_time BETWEEN ? AND ?");
                params.add(dates[0]);
                params.add(dates[1]);
            }
        }
        // 分页查询
        int offset = (current - 1) * pageSize;
        sql.append(" LIMIT ?, ?");
        params.add(offset);
        params.add(pageSize);
        // 执行查询SQL
        List<Map<String, Object>> data = executeSQL(sql.toString(), params.toArray());
        // 查询总条数
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) AS total FROM fault_report fr LEFT JOIN project p ON fr.project_id = p.id WHERE 1=1");
        List<Object> countParams = new ArrayList<>();
        if (projectName != null && !projectName.isEmpty()) {
            countSql.append(" AND p.project_name = ?");
            countParams.add(projectName);
        }
        if (faultCategory != null && !faultCategory.isEmpty()) {
            countSql.append(" AND fr.fault_category = ?");
            countParams.add(faultCategory);
        }
        if (dateRange != null && !dateRange.isEmpty()) {
            String[] dates = dateRange.split(" - ");
            if (dates.length == 2) {
                countSql.append(" AND fr.report_time BETWEEN ? AND ?");
                countParams.add(dates[0]);
                countParams.add(dates[1]);
            }
        }
        List<Map<String, Object>> countResult = executeSQL(countSql.toString(), countParams.toArray());
        int total = countResult.isEmpty() ? 0 : Integer.parseInt(String.valueOf(countResult.get(0).get("total")));
        // 返回结果
        Result result = new Result();
        result.setFlag(true);
        result.setMessage("查询成功");
        Map<String, Object> resultData = new HashMap<>();
        resultData.put("total", total);
        resultData.put("data", data);
        result.setData(resultData);
        return result;
    }


    public Result getCurrentUser(Map<String, Object> paramMap) {
        // 获取当前登录用户的名称
        String currentUserName = sdkOfGetCurrentUserName();
        // 构建查询SQL
        StringBuilder sql = new StringBuilder("SELECT id, serial_number, project_id, system_device_name, device_name, fault_category, fault_description, affect_startup, reporter, report_time, current_status, fault_handling_duration, operation, create_username, create_time, update_username, update_time FROM fault_list WHERE reporter = ?");
        // 执行SQL查询
        List<Map<String, Object>> result = executeSQL(sql.toString(), currentUserName);
        // 构建返回结果
        Result res = new Result();
        res.setFlag(true);
        res.setMessage("查询成功");
        res.setData(result);
        return res;
    }

    public Result getCurrentTime(Map<String, Object> paramMap) {
        // 获取当前时间
        String currentTime = java.time.LocalDateTime.now().toString();
        // 构建返回结果
        Result result = new Result();
        result.setFlag(true);
        result.setMessage("获取当前时间成功");
        result.setData(currentTime);
        return result;
    }
}
